import asyncio
import json
import logging
import os
import uuid
from typing import Any, AsyncGenerator, Dict, Optional

import gradio as gr

# from browser_use.agent.service import Agent
from browser_use.agent.views import (
    AgentHistoryList,
    AgentOutput,
)
from browser_use.browser.browser import BrowserConfig
from browser_use.browser.context import BrowserContext, BrowserContextConfig
from browser_use.browser.views import BrowserState
from gradio.components import Component
from langchain_core.language_models.chat_models import BaseChatModel

from browser.agent.browser_use.browser_use_agent import BrowserUseAgent
from browser.browser.custom_browser import CustomBrowser
from browser.controller.custom_controller import CustomController
from browser.utils import llm_provider
from browser.webui.webui_manager import WebuiManager
from browser.api.db.model_service import process_directory

logger = logging.getLogger(__name__)

def _format_agent_output(model_output: AgentOutput) -> str:
    """Formats AgentOutput for display in the chatbot using JSON."""
    content = ""
    if model_output:
        try:
            # Directly use model_dump if actions and current_state are Pydantic models
            action_dump = [
                action.model_dump(exclude_none=True) for action in model_output.action
            ]

            state_dump = model_output.current_state.model_dump(exclude_none=True)
            model_output_dump = {
                "current_state": state_dump,
                "action": action_dump,
            }
            # Dump to JSON string with indentation
            json_string = json.dumps(model_output_dump, indent=4, ensure_ascii=False)
            # Wrap in <pre><code> for proper display in HTML
            content = f"<pre><code class='language-json'>{json_string}</code></pre>"

        except AttributeError as ae:
            logger.error(
                f"AttributeError during model dump: {ae}. Check if 'action' or 'current_state' or their items support 'model_dump'."
            )
            content = f"<pre><code>Error: Could not format agent output (AttributeError: {ae}).\nRaw output: {str(model_output)}</code></pre>"
        except Exception as e:
            logger.error(f"Error formatting agent output: {e}", exc_info=True)
            # Fallback to simple string representation on error
            content = f"<pre><code>Error formatting agent output.\nRaw output:\n{str(model_output)}</code></pre>"

    return content.strip()
async def _initialize_llm(
        provider: Optional[str],
        model_name: Optional[str],
        temperature: float,
        base_url: Optional[str],
        api_key: Optional[str],
        num_ctx: Optional[int] = None,
) -> Optional[BaseChatModel]:
    """Initializes the LLM based on settings. Returns None if provider/model is missing."""
    if not provider or not model_name:
        logger.info("LLM Provider or Model Name not specified, LLM will be None.")
        return None
    try:
        # Use your actual LLM provider logic here
        logger.info(
            f"Initializing LLM: Provider={provider}, Model={model_name}, Temp={temperature}"
        )
        # Example using a placeholder function
        llm = llm_provider.get_llm_model(
            provider=provider,
            model_name=model_name,
            temperature=temperature,
            base_url=base_url or None,
            api_key=api_key or None,
            # Add other relevant params like num_ctx for ollama
            num_ctx=num_ctx if provider == "ollama" else None,
        )
        return llm
    except Exception as e:
        logger.error(f"Failed to initialize LLM: {e}", exc_info=True)
        gr.Warning(
            f"Failed to initialize LLM '{model_name}' for provider '{provider}'. Please check settings. Error: {e}"
        )
        return None

def _handle_done(webui_manager: WebuiManager, history: AgentHistoryList):
    """Callback when the agent finishes the task (success or failure)."""
    logger.info(
        f"Agent task finished. Duration: {history.total_duration_seconds():.2f}s, Tokens: {history.total_input_tokens()}"
    )
    final_summary = "**Task Completed**\n"
    final_summary += f"- Duration: {history.total_duration_seconds():.2f} seconds\n"
    final_summary += f"- Total Input Tokens: {history.total_input_tokens()}\n"  # Or total tokens if available

    final_result = history.final_result()
    # 结果处理
    if final_result:
        webui_manager.json_result = final_result
        final_summary += f"- Final Result: {final_result}\n"

    errors = history.errors()
    if errors and any(errors):
        final_summary += f"- **Errors:**\n```\n{errors}\n```\n"
    else:
        final_summary += "- Status: Success\n"

    webui_manager.bu_chat_history.append(
        {"role": "assistant", "content": final_summary}
    )


async def _ask_assistant_callback(
        webui_manager: WebuiManager, query: str, browser_context: BrowserContext
) -> Dict[str, Any]:
    """Callback triggered by the agent's ask_for_assistant action."""
    logger.info("Agent requires assistance. Waiting for user input.")

    if not hasattr(webui_manager, "_chat_history"):
        logger.error("Chat history not found in webui_manager during ask_assistant!")
        return {"response": "Internal Error: Cannot display help request."}

    webui_manager.bu_chat_history.append(
        {
            "role": "assistant",
            "content": f"**Need Help:** {query}\nPlease provide information or perform the required action in the browser, then type your response/confirmation below and click 'Submit Response'.",
        }
    )

    # Use state stored in webui_manager
    webui_manager.bu_response_event = asyncio.Event()
    webui_manager.bu_user_help_response = None  # Reset previous response

    try:
        logger.info("Waiting for user response event...")
        await asyncio.wait_for(
            webui_manager.bu_response_event.wait(), timeout=3600.0
        )  # Long timeout
        logger.info("User response event received.")
    except asyncio.TimeoutError:
        logger.warning("Timeout waiting for user assistance.")
        webui_manager.bu_chat_history.append(
            {
                "role": "assistant",
                "content": "**Timeout:** No response received. Trying to proceed.",
            }
        )
        webui_manager.bu_response_event = None  # Clear the event
        return {"response": "Timeout: User did not respond."}  # Inform the agent

    response = webui_manager.bu_user_help_response
    webui_manager.bu_chat_history.append(
        {"role": "user", "content": response}
    )  # Show user response in chat
    webui_manager.bu_response_event = (
        None  # Clear the event for the next potential request
    )
    return {"response": response}

async def _handle_new_step(
        webui_manager: WebuiManager, state: BrowserState, output: AgentOutput, step_num: int
):
    """Callback for each step taken by the agent, including screenshot display."""
    await asyncio.sleep(3)
    # Use the correct chat history attribute name from the user's code
    if not hasattr(webui_manager, "bu_chat_history"):
        logger.error(
            "Attribute 'bu_chat_history' not found in webui_manager! Cannot add chat message."
        )
        # Initialize it maybe? Or raise an error? For now, log and potentially skip chat update.
        webui_manager.bu_chat_history = []  # Initialize if missing (consider if this is the right place)
        # return # Or stop if this is critical
    step_num -= 1
    logger.info(f"Step {step_num} completed.")

    # --- Screenshot Handling ---
    screenshot_html = ""
    # Ensure state.screenshot exists and is not empty before proceeding
    # Use getattr for safer access
    screenshot_data = getattr(state, "screenshot", None)
    if screenshot_data:
        try:
            # Basic validation: check if it looks like base64
            if (
                    isinstance(screenshot_data, str) and len(screenshot_data) > 100
            ):  # Arbitrary length check
                # *** UPDATED STYLE: Removed centering, adjusted width ***
                img_tag = f'<img src="data:image/jpeg;base64,{screenshot_data}" alt="Step {step_num} Screenshot" style="max-width: 800px; max-height: 600px; object-fit:contain;" />'
                screenshot_html = (
                        img_tag + "<br/>"
                )  # Use <br/> for line break after inline-block image
            else:
                logger.warning(
                    f"Screenshot for step {step_num} seems invalid (type: {type(screenshot_data)}, len: {len(screenshot_data) if isinstance(screenshot_data, str) else 'N/A'})."
                )
                screenshot_html = "**[Invalid screenshot data]**<br/>"

        except Exception as e:
            logger.error(
                f"Error processing or formatting screenshot for step {step_num}: {e}",
                exc_info=True,
            )
            screenshot_html = "**[Error displaying screenshot]**<br/>"
    else:
        logger.debug(f"No screenshot available for step {step_num}.")

    # --- Format Agent Output ---
    print(f"输出1=========>{output}")
    
    # 将output写入数据库sjml_records表
    try:
        from app.services.conversations import ConversationsService
        import json
        
        # 准备数据
        data = {
            'conversation_id': webui_manager.bu_conversation_id or str(uuid.uuid4()),  # 优先使用webui_manager中存储的会话ID
            'user_id': 'system_user',  # 默认用户ID
            'data_conf_id': str(uuid.uuid4()),  # 生成随机数据配置ID
            'step_title': f'Step {step_num}',  # 步骤标题
            'current_state': str(output),  # 控制台输出内容
            'status': 1,  # 默认状态
            'is_delete': 0,  # 默认不删除
            'message_type': 'assistan'  # 默认消息类型
        }
        
        # 调用服务层方法写入数据库
        ConversationsService.create_record(data)
        print(f"数据已成功写入数据库，步骤: Step {step_num}，conversation_id: {data['conversation_id']}")
    except Exception as e:
        print(f"写入数据库失败: {str(e)}")
        
    formatted_output = _format_agent_output(output)  # Use the updated function

    # --- Combine and Append to Chat ---
    step_header = f"--- **Step {step_num}** ---"
    print("cccccccccccccc2")
    # Combine header, image (with line break), and JSON block
    final_content = step_header + "<br/>" + screenshot_html + formatted_output

    chat_message = {
        "role": "assistant",
        "content": final_content.strip(),  # Remove leading/trailing whitespace
    }

    # Append to the correct chat history list
    webui_manager.bu_chat_history.append(chat_message)

    await asyncio.sleep(2)

async def run_task(task: str, webui_manager: WebuiManager):
    webui_manager.bu_chat_history.append({"role": "user", "content": task})

    llm_provider_name = "alibaba"
    llm_model_name = "qwen3-max-preview"
    llm_temperature = 0.6
    llm_base_url = "https://dashscope.aliyuncs.com/compatible-mode/v1"
    llm_api_key = "sk-6232878a9baf4507b9e2e4273bc24f01"
    main_llm = await _initialize_llm(
        llm_provider_name,
        llm_model_name,
        llm_temperature,
        llm_base_url,
        llm_api_key,
        None,
    )


    async def ask_callback_wrapper(
            query: str, browser_context: BrowserContext
    ) -> Dict[str, Any]:
        return await _ask_assistant_callback(webui_manager, query, browser_context)

    if not webui_manager.bu_controller:
        webui_manager.bu_controller = CustomController(
            ask_assistant_callback=ask_callback_wrapper
        )
        # await webui_manager.bu_controller.setup_mcp_client(mcp_server_config)
    should_close_browser_on_finish = True
    try:
        if should_close_browser_on_finish:
            # Close existing resources if not keeping open
            if webui_manager.bu_browser_context:
                logger.info("Closing previous browser context.")
                await webui_manager.bu_browser_context.close()
                webui_manager.bu_browser_context = None
            if webui_manager.bu_browser:
                logger.info("Closing previous browser.")
                await webui_manager.bu_browser.close()
                webui_manager.bu_browser = None

        # Create Browser if needed
        if not webui_manager.bu_browser:
            logger.info("Launching new browser instance.")
            extra_args = []
            browser_binary_path = None

            webui_manager.bu_browser = CustomBrowser(
                config=BrowserConfig(
                    headless=False,
                    disable_security=False,
                    browser_binary_path=browser_binary_path,
                    extra_browser_args=extra_args,
                    wss_url=None,
                    cdp_url=None,
                    new_context_config=BrowserContextConfig(
                        window_width=1280,
                        window_height=1100,
                    )
                )
            )

        # Create Context if needed
        if not webui_manager.bu_browser_context:
            logger.info("Creating new browser context.")
            context_config = BrowserContextConfig(
                trace_path=None,
                save_recording_path=None,
                save_downloads_path=None,
                window_height=1280,
                window_width=1100,
            )
            if not webui_manager.bu_browser:
                raise ValueError("Browser not initialized, cannot create context.")
            webui_manager.bu_browser_context = (
                await webui_manager.bu_browser.new_context(config=context_config)
            )

        # --- 5. Initialize or Update Agent ---
        webui_manager.bu_agent_task_id = str(uuid.uuid4())  # New ID for this task run
        # os.makedirs(
        #     os.path.join(save_agent_history_path, webui_manager.bu_agent_task_id),
        #     exist_ok=True,
        # )
        # history_file = os.path.join(
        #     save_agent_history_path,
        #     webui_manager.bu_agent_task_id,
        #     f"{webui_manager.bu_agent_task_id}.json",
        # )
        # gif_path = os.path.join(
        #     save_agent_history_path,
        #     webui_manager.bu_agent_task_id,
        #     f"{webui_manager.bu_agent_task_id}.gif",
        # )

        # Pass the webui_manager to callbacks when wrapping them
        async def step_callback_wrapper(
                state: BrowserState, output: AgentOutput, step_num: int
        ):
            await _handle_new_step(webui_manager, state, output, step_num)

        def done_callback_wrapper(history: AgentHistoryList):
            _handle_done(webui_manager, history)

        if not webui_manager.bu_agent:
            logger.info(f"Initializing new agent for task: {task}")
            if not webui_manager.bu_browser or not webui_manager.bu_browser_context:
                raise ValueError(
                    "Browser or Context not initialized, cannot create agent."
                )
            webui_manager.bu_agent = BrowserUseAgent(
                task=task,
                llm=main_llm,
                browser=webui_manager.bu_browser,
                browser_context=webui_manager.bu_browser_context,
                controller=webui_manager.bu_controller,
                register_new_step_callback=step_callback_wrapper,
                register_done_callback=done_callback_wrapper,
                use_vision=False,
                override_system_message=None,
                extend_system_message="对于输出的过程数据采用中文输出，保证返回的json格式可以正确解析,extract_content属性下，需要包含should_strip_link_urls: False",
                max_input_tokens=128000,
                max_actions_per_step=10,
                tool_calling_method='function_calling',
                planner_llm=None,
                use_vision_for_planner=False,
                source="webui",
            )
            webui_manager.bu_agent.state.agent_id = webui_manager.bu_agent_task_id
            # webui_manager.bu_agent.settings.generate_gif = gif_path
        else:
            webui_manager.bu_agent.state.agent_id = webui_manager.bu_agent_task_id
            webui_manager.bu_agent.add_new_task(task)
            # webui_manager.bu_agent.settings.generate_gif = gif_path
            webui_manager.bu_agent.browser = webui_manager.bu_browser
            webui_manager.bu_agent.browser_context = webui_manager.bu_browser_context
            webui_manager.bu_agent.controller = webui_manager.bu_controller

        # --- 6. Run Agent Task and Stream Updates ---
        agent_run_coro = webui_manager.bu_agent.run(max_steps=100)
        agent_task = asyncio.create_task(agent_run_coro)
        webui_manager.bu_current_task = agent_task  # Store the task

        last_chat_len = len(webui_manager.bu_chat_history)
        while not agent_task.done():
            is_paused = webui_manager.bu_agent.state.paused
            is_stopped = webui_manager.bu_agent.state.stopped

            # Check if agent is asking for help (via response_event)
            update_dict = {}
            if webui_manager.bu_response_event is not None:
                update_dict = {
                    # user_input_comp: gr.update(
                    #     placeholder="Agent needs help. Enter response and submit.",
                    #     interactive=True,
                    # ),
                    # run_button_comp: gr.update(
                    #     value="✔️ Submit Response", interactive=True
                    # ),
                    # pause_resume_button_comp: gr.update(interactive=False),
                    # stop_button_comp: gr.update(interactive=False),
                    # chatbot_comp: gr.update(value=webui_manager.bu_chat_history),
                }
                last_chat_len = len(webui_manager.bu_chat_history)
                # yield update_dict
                # Wait until response is submitted or task finishes
                while (
                        webui_manager.bu_response_event is not None
                        and not agent_task.done()
                ):
                    await asyncio.sleep(0.2)
                # Restore UI after response submitted or if task ended unexpectedly
                if not agent_task.done():
                    pass
                    # yield {
                    #     user_input_comp: gr.update(
                    #         placeholder="Agent is running...", interactive=False
                    #     ),
                    #     run_button_comp: gr.update(
                    #         value="⏳ Running...", interactive=False
                    #     ),
                    #     pause_resume_button_comp: gr.update(interactive=True),
                    #     stop_button_comp: gr.update(interactive=True),
                    # }
                else:
                    break  # Task finished while waiting for response

            # Update Chatbot if new messages arrived via callbacks
            # if len(webui_manager.bu_chat_history) > last_chat_len:
            #     update_dict[chatbot_comp] = gr.update(
            #         value=webui_manager.bu_chat_history
            #     )
            #     last_chat_len = len(webui_manager.bu_chat_history)

            # Update Browser View
            # update_dict[browser_view_comp] = gr.update(visible=False)

            # Yield accumulated updates
            # if update_dict:
            #     yield update_dict

            await asyncio.sleep(0.1)  # Polling interval

        # --- 7. Task Finalization ---
        webui_manager.bu_agent.state.paused = False
        webui_manager.bu_agent.state.stopped = False
        final_update = {}
        try:
            logger.info("Agent task completing...")
            # Await the task ensure completion and catch exceptions if not already caught
            if not agent_task.done():
                await agent_task  # Retrieve result/exception
            elif agent_task.exception():  # Check if task finished with exception
                agent_task.result()  # Raise the exception to be caught below
            logger.info("Agent task completed processing.")
            # return json.loads(json_result)
            # logger.info(f"Explicitly saving agent history to: {history_file}")
            # webui_manager.bu_agent.save_history(history_file)

            # if os.path.exists(history_file):
            #     final_update[history_file_comp] = gr.File(value=history_file)

            # if gif_path and os.path.exists(gif_path):
            #     logger.info(f"GIF found at: {gif_path}")
            #     final_update[gif_comp] = gr.Image(value=gif_path)

        except asyncio.CancelledError:
            logger.info("Agent task was cancelled.")
            if not any(
                    "Cancelled" in msg.get("content", "")
                    for msg in webui_manager.bu_chat_history
                    if msg.get("role") == "assistant"
            ):
                webui_manager.bu_chat_history.append(
                    {"role": "assistant", "content": "**Task Cancelled**."}
                )
            # final_update[chatbot_comp] = gr.update(value=webui_manager.bu_chat_history)
        except Exception as e:
            logger.error(f"Error during agent execution: {e}", exc_info=True)
            error_message = (
                f"**Agent Execution Error:**\n```\n{type(e).__name__}: {e}\n```"
            )
            if not any(
                    error_message in msg.get("content", "")
                    for msg in webui_manager.bu_chat_history
                    if msg.get("role") == "assistant"
            ):
                webui_manager.bu_chat_history.append(
                    {"role": "assistant", "content": error_message}
                )
            # final_update[chatbot_comp] = gr.update(value=webui_manager.bu_chat_history)
            gr.Error(f"Agent execution failed: {e}")

        finally:
            webui_manager.bu_current_task = None  # Clear the task reference

            # Close browser/context if requested
            if should_close_browser_on_finish:
                if webui_manager.bu_browser_context:
                    logger.info("Closing browser context after task.")
                    await webui_manager.bu_browser_context.close()
                    webui_manager.bu_browser_context = None
                if webui_manager.bu_browser:
                    logger.info("Closing browser after task.")
                    await webui_manager.bu_browser.close()
                    webui_manager.bu_browser = None

            # --- 8. Final UI Update ---
            # final_update.update(
            #     {
            #         user_input_comp: gr.update(
            #             value="",
            #             interactive=True,
            #             placeholder="Enter your next task...",
            #         ),
            #         run_button_comp: gr.update(value="▶️ Submit Task", interactive=True),
            #         stop_button_comp: gr.update(value="⏹️ Stop", interactive=False),
            #         pause_resume_button_comp: gr.update(
            #             value="⏸️ Pause", interactive=False
            #         ),
            #         clear_button_comp: gr.update(interactive=True),
            #         # Ensure final chat history is shown
            #         chatbot_comp: gr.update(value=webui_manager.bu_chat_history),
            #     }
            # )
            # yield final_update

    except Exception as e:
        # Catch errors during setup (before agent run starts)
        logger.error(f"Error setting up agent task: {e}", exc_info=True)
        webui_manager.bu_current_task = None  # Ensure state is reset
        # yield {
        #     user_input_comp: gr.update(
        #         interactive=True, placeholder="Error during setup. Enter task..."
        #     ),
        #     run_button_comp: gr.update(value="▶️ Submit Task", interactive=True),
        #     stop_button_comp: gr.update(value="⏹️ Stop", interactive=False),
        #     pause_resume_button_comp: gr.update(value="⏸️ Pause", interactive=False),
        #     clear_button_comp: gr.update(interactive=True),
        #     chatbot_comp: gr.update(
        #         value=webui_manager.bu_chat_history
        #               + [{"role": "assistant", "content": f"**Setup Error:** {e}"}]
        #     ),
        # }
import asyncio
import requests
from datetime import datetime, timedelta, timezone

# 常量定义
SQL_API_URL = "http://25.41.36.247:8080/api/rds/getSql"
DEFAULT_PAGE_SIZE = 100
API_PARAMS_BASE = {
    "accesskeyId": "33mEycshEKbhgPpU",
    "accesskeySecret": "Iem7CToROVZjWs3lOgMEY3kVkxBTyv",
    "action": "DescribeSQLLogRecords",
    "regionId": "sd-1",
    "dbInstanceId": "rm-8vlje45fk59hyzmfj",
    "department": "581"
}

async def collect_menu_sql_data(system_config, conversation_id=None):
    """收集菜单数据并获取对应的SQL语句
    
    Args:
        system_config: 系统配置信息
        conversation_id: 会话ID
        
    Returns:
        dict: 包含菜单及其对应SQL语句的数据结构
    """
    try:
        # 创建WebuiManager实例并存储conversation_id
        webui_manager = WebuiManager()
        webui_manager.init_browser_use_agent()
        webui_manager.bu_conversation_id = conversation_id
        
        # 获取菜单数据
        menu_data = await _fetch_menu_data(system_config, conversation_id)
        if not menu_data:
            print("未获取到菜单数据")
            return {}
        
        # 写入菜单数据到数据库
        from browser.api.db.model_service import db_ops
        system_name = system_config.get('name', '未知系统')
        system_id = system_config.get('id', 1)
        
        print("===============菜单数据:", menu_data)

        def write_menu_to_db(menu_data, parent_path=None, parent_id=None):
            """递归写入菜单数据到数据库"""
            if parent_path is None:
                parent_path = []
            
            # menu_data 应该是一个列表
            for menu in menu_data:
                node_name = menu["名称"]
                # 当前节点的完整路径
                current_path = parent_path + [node_name]
                
                # 获取层级深度
                level = len(current_path)
                
                # 写入菜单到数据库
                menu_obj = db_ops.create_menu(node_name, level, parent_id, system_name, system_id)
                
                # 递归处理下一级菜单
                # 先判断“二级菜单”和“三级菜单”是否存在
                if '二级菜单' in menu and menu['二级菜单']:
                    write_menu_to_db(menu['二级菜单'], current_path, menu_obj.id)
                if '三级菜单' in menu and menu['三级菜单']:
                    write_menu_to_db(menu['三级菜单'], current_path, menu_obj.id)
        
        # 执行写入操作
        print("开始写入菜单数据到数据库...")
        write_menu_to_db(menu_data)
        print("菜单数据写入完成")
        
        # 继续处理SQL收集
        result_data = await _process_menu_sql_collection(menu_data, system_config, conversation_id)
        print("最终收集的数据:", result_data)
        return result_data
    except Exception as e:
        print(f"收集菜单SQL数据时发生错误: {e}")
        return {}

async def _fetch_menu_data(system_config, conversation_id=None):
    """获取系统菜单数据"""
    webui_manager = WebuiManager()
    webui_manager.init_browser_use_agent()
    if conversation_id:
        webui_manager.bu_conversation_id = conversation_id
    try:
        prompt = f"""
        打开{system_config['address']}，
        输入用户名{system_config['username']}密码{system_config['password']}，点击登录按钮，并等待3秒，
        点击确认待页面加载完成后继续后续操作，登录后使用extract_content方法获取登录后页面的一级菜单，二级菜单和三级菜单，
        以json格式返回,返回的json数据中不要包含\\n,并保证json可以被正常解析，返回的json格式如下：
        {{
          "一级菜单": [
            {{"名称": "xxxx", "二级菜单": []}},
          ]
        }}
        """
        await run_task(prompt, webui_manager)
        
        data_str = webui_manager.json_result
        print(f"获取到的菜单原始数据: {data_str}==========")
        return _parse_menu_data(data_str)
    except Exception as e:
        print(f"获取菜单数据时发生错误: {e}")
        return None
    

def _parse_menu_data(data_str):
    """解析菜单JSON数据"""
    try:
        json_data = json.loads(data_str)
        
        # 确保数据格式正确
        if not isinstance(json_data, dict):
            json_data = json.loads(json_data)
            
        json_list = json_data.get("一级菜单", [])
        
        # 确保菜单列表格式正确
        if not isinstance(json_list, list):
            json_list = json.loads(json_list)
            
        return json_list
    except Exception as e:
        print(f"解析菜单数据时发生错误: {e}")
        print(f"错误数据: {data_str}")
        return []

async def _process_menu_sql_collection(menu_list, system_config, conversation_id=None):
    """处理菜单列表并收集对应的SQL语句"""
    data = {}
    
    for menu_item in menu_list:
        # 确保菜单项是字典格式
        if not isinstance(menu_item, dict):
            try:
                menu_item = json.loads(menu_item)
            except Exception as e:
                print(f"解析菜单项时发生错误: {e}")
                print(f"错误菜单项: {menu_item}")
                continue
        
        # 处理有二级菜单的情况
        if menu_item.get("二级菜单", []):
            await _process_menu_with_submenus(menu_item, data, system_config, conversation_id)
        else:
            # 处理没有二级菜单的情况（当前代码中有continue，暂时跳过这部分逻辑）
            continue
    
    return data

async def _process_menu_with_submenus(menu_item, data, system_config, conversation_id=None):
    """处理带有二级菜单的菜单项"""
    menu_name = menu_item.get("名称")
    
    # 目前只处理"数据目录管理"菜单
    if menu_name != "数据目录管理":
        return
    
    data[menu_name] = {}
    submenus = menu_item.get("二级菜单", [])
    
    for submenu in submenus:
        submenu_name = submenu.get("名称", "").strip()
        if not submenu_name:
            continue
        
        print(f"处理菜单: {menu_name} -> {submenu_name}")
        sql_statements = await _get_sql_for_menu_item(menu_name, submenu_name, system_config, conversation_id)
        data[menu_name][submenu_name] = sql_statements

async def _get_sql_for_menu_item(menu_name, submenu_name, system_config, conversation_id=None):
    """获取特定菜单项操作产生的SQL语句"""
    # 记录操作开始时间
    start_time = datetime.now(timezone.utc)
    
    # 执行菜单操作
    try:
        await _perform_menu_operation(menu_name, submenu_name, system_config, conversation_id)
    except Exception as e:
        print(f"执行菜单操作时发生错误: {e}")
        return []
    
    # 等待操作完成并记录结束时间
    await asyncio.sleep(10)
    end_time = datetime.now(timezone.utc)
    
    # 获取并过滤SQL日志
    sql_statements = await _fetch_and_filter_sql_logs(start_time, end_time)
    
    return sql_statements

async def _perform_menu_operation(menu_name, submenu_name, system_config, conversation_id=None):
    """执行菜单操作"""
    webui_manager = WebuiManager()
    webui_manager.init_browser_use_agent()
    
    # 如果提供了conversation_id，则存储到webui_manager中
    if conversation_id:
        webui_manager.bu_conversation_id = conversation_id
        print(f"已设置conversation_id: {conversation_id}")
    
    await run_task(f"""
    打开{system_config['address']}，
    输入用户名{system_config['username']}密码{system_config['password']}，点击登录按钮,待页面加载完成后继续后续操作。
    点击 {menu_name}一级菜单下的{submenu_name}菜单，并点击页面中的查看，查询，详情按钮，其他按钮不用点击。
    """, webui_manager)

async def _fetch_and_filter_sql_logs(start_time, end_time):
    """获取并过滤SQL日志"""
    # 准备API请求参数
    params = API_PARAMS_BASE.copy()
    params.update({
        "startTime": start_time.strftime("%Y-%m-%dT%H:%M:%SZ"),
        "endTime": end_time.strftime("%Y-%m-%dT%H:%M:%SZ"),
        "pageSize": DEFAULT_PAGE_SIZE,
        "pageNumber": 1
    })
    
    # 获取所有SQL记录
    sql_records = []
    try:
        # 获取第一页数据
        response = requests.get(SQL_API_URL, params=params)
        response.raise_for_status()
        response_json = response.json()
        
        total_record_count = response_json.get("TotalRecordCount", 0)
        print(f"总记录数: {total_record_count}")
        
        # 添加第一页的记录
        if "Items" in response_json and "SQLRecord" in response_json["Items"]:
            sql_records.extend(response_json["Items"]["SQLRecord"])
        
        # 获取剩余页数的数据
        total_pages = (total_record_count + DEFAULT_PAGE_SIZE - 1) // DEFAULT_PAGE_SIZE
        for page_num in range(2, total_pages + 1):
            params['pageNumber'] = page_num
            response = requests.get(SQL_API_URL, params=params)
            response.raise_for_status()
            response_json = response.json()
            
            if "Items" in response_json and "SQLRecord" in response_json["Items"]:
                sql_records.extend(response_json["Items"]["SQLRecord"])
    except Exception as e:
        print(f"获取SQL日志时发生错误: {e}")
    
    print(f"总共获取到的SQL记录数: {len(sql_records)}")
    
    # 过滤SQL记录
    return _filter_sql_statements(sql_records)

def _filter_sql_statements(sql_records):
    """过滤出需要的SQL语句"""
    all_sql_texts = []
    filtered_sql_texts = []
    
    for sql_record in sql_records:
        sql_text = sql_record.get("SQLText", "")
        all_sql_texts.append(sql_text)
        
        # 过滤条件：SELECT或UPDATE语句，且不包含特定的表名和注释
        if ("select" in sql_text.lower() or "update" in sql_text.lower()) and \
           "qrtz_scheduler_state" not in sql_text.lower() and \
           "qrtz_triggers" not in sql_text.lower() and \
           "/*" not in sql_text and \
           "@@session" not in sql_text.lower():
            filtered_sql_texts.append(sql_text)
    
    print(f"过滤后保留的SQL语句数量: {len(filtered_sql_texts)}")
    return filtered_sql_texts
